clear all
set more off
set mem 10000000
set matsize 10000
version 13

***********************************************************************************
*** Build File to Convert ArcGIS Output into Village-Level Nightlights Datasets ***
***********************************************************************************

** Set file paths
do "$path_code/paths.do"

********************************************************************************
********************************************************************************

** Step 1: Read average visible lights into Stata
{
cd "$nl/nightlights_avg_vis/andhra pradesh"
local ap_files: dir "$nl/nightlights_avg_vis/andhra pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "andhra pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/assam"
local ap_files: dir "$nl/nightlights_avg_vis/assam" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "assam"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/bihar"
local ap_files: dir "$nl/nightlights_avg_vis/bihar" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "bihar"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/chhattisgarh"
local ap_files: dir "$nl/nightlights_avg_vis/chhattisgarh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "chhattisgarh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/goa"
local ap_files: dir "$nl/nightlights_avg_vis/goa" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "goa"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/gujarat"
local ap_files: dir "$nl/nightlights_avg_vis/gujarat" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "gujarat"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/haryana"
local ap_files: dir "$nl/nightlights_avg_vis/haryana" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "haryana"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/himachal pradesh"
local ap_files: dir "$nl/nightlights_avg_vis/himachal pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "himachal pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/jammu kashmir"
local ap_files: dir "$nl/nightlights_avg_vis/jammu kashmir" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "jammu kashmir"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/jharkhand"
local ap_files: dir "$nl/nightlights_avg_vis/jharkhand" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "jharkhand"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/karnataka"
local ap_files: dir "$nl/nightlights_avg_vis/karnataka" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "karnataka"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/kerala"
local ap_files: dir "$nl/nightlights_avg_vis/kerala" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "kerala"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/madhya pradesh"
local ap_files: dir "$nl/nightlights_avg_vis/madhya pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "madhya pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/maharashtra"
local ap_files: dir "$nl/nightlights_avg_vis/maharashtra" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "maharashtra"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/manipur"
local ap_files: dir "$nl/nightlights_avg_vis/manipur" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "manipur"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/orissa"
local ap_files: dir "$nl/nightlights_avg_vis/orissa" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "orissa"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/punjab"
local ap_files: dir "$nl/nightlights_avg_vis/punjab" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "punjab"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/rajasthan"
local ap_files: dir "$nl/nightlights_avg_vis/rajasthan" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "rajasthan"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/tamil nadu"
local ap_files: dir "$nl/nightlights_avg_vis/tamil nadu" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "tamil nadu"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/tripura"
local ap_files: dir "$nl/nightlights_avg_vis/tripura" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "tripura"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/uttar pradesh"
local ap_files: dir "$nl/nightlights_avg_vis/uttar pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttar pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
    drop zstats*
	 rename *_c_code01 c_code01
    drop objectid  orig_fid
	cap drop up_*
	cap drop uttar_pradesh_*
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/uttar pradesh"
local ap_files: dir "$nl/nightlights_avg_vis/uttar pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttar pradesh"
  rename *,lower
  gen extra = substr(prelimstring, -9, 5)
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
    gen up_version = .
    replace up_version = 1 if strpos("`file'", "east1")
    replace up_version = 2 if strpos("`file'", "east2")
    replace up_version = 3 if strpos("`file'", "west")
    local up_version = up_version
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid orig_fid
	cap drop up_*
	cap drop uttar_pradesh_*
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`up_version'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  gen up_version = .
  replace up_version = 1 if strpos("`file'", "east1")
  replace up_version = 2 if strpos("`file'", "east2")
  replace up_version = 3 if strpos("`file'", "west")
  local up_version = up_version
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`up_version'_`sat'_`year'.dta", replace
}

}  
  
  
cd "$nl/nightlights_avg_vis/uttarakhand"
local ap_files: dir "$nl/nightlights_avg_vis/uttarakhand" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttarakhand"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_avg_vis/west bengal"
local ap_files: dir "$nl/nightlights_avg_vis/west bengal" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "west bengal"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_avg_vis/all_stata/`state'_`sat'_`year'.dta", replace
}

}


*** Append all stata files
clear
cd "$nl/nightlights_avg_vis/all_stata"
local nightlights: dir "$nl/nightlights_avg_vis/all_stata" files "*.dta"
foreach file in `nightlights' {
  use "`file'", clear
  tostring *, replace force
  save, replace
}
local nightlights: dir "$nl/nightlights_avg_vis/all_stata" files "*.dta"
foreach file in `nightlights' {
  append using "`file'"
}
drop  up_version area prelimstring extra 
duplicates drop
drop if c_code01 == ""
destring lights_* year, replace 
save "$nl/nightlights_avg_vis/nightlights_all_avg_vis.dta", replace

}

********************************************************************************
********************************************************************************

** Step 2: Read stable lights into Stata
{
cd "$nl/nightlights_stable/andhra pradesh"
local ap_files: dir "$nl/nightlights_stable/andhra pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "andhra pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/assam"
local ap_files: dir "$nl/nightlights_stable/assam" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "assam"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/bihar"
local ap_files: dir "$nl/nightlights_stable/bihar" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "bihar"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/chhattisgarh"
local ap_files: dir "$nl/nightlights_stable/chhattisgarh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "chhattisgarh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/goa"
local ap_files: dir "$nl/nightlights_stable/goa" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "goa"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/gujarat"
local ap_files: dir "$nl/nightlights_stable/gujarat" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "gujarat"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/haryana"
local ap_files: dir "$nl/nightlights_stable/haryana" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "haryana"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/himachal pradesh"
local ap_files: dir "$nl/nightlights_stable/himachal pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "himachal pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/jammu kashmir"
local ap_files: dir "$nl/nightlights_stable/jammu kashmir" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "jammu kashmir"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/jharkhand"
local ap_files: dir "$nl/nightlights_stable/jharkhand" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "jharkhand"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/karnataka"
local ap_files: dir "$nl/nightlights_stable/karnataka" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "karnataka"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/kerala"
local ap_files: dir "$nl/nightlights_stable/kerala" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "kerala"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/madhya pradesh"
local ap_files: dir "$nl/nightlights_stable/madhya pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "madhya pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/maharashtra"
local ap_files: dir "$nl/nightlights_stable/maharashtra" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "maharashtra"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/manipur"
local ap_files: dir "$nl/nightlights_stable/manipur" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "manipur"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/orissa"
local ap_files: dir "$nl/nightlights_stable/orissa" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "orissa"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/punjab"
local ap_files: dir "$nl/nightlights_stable/punjab" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "punjab"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/rajasthan"
local ap_files: dir "$nl/nightlights_stable/rajasthan" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "rajasthan"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/tamil nadu"
local ap_files: dir "$nl/nightlights_stable/tamil nadu" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "tamil nadu"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/tripura"
local ap_files: dir "$nl/nightlights_stable/tripura" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "tripura"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/uttar pradesh"
local ap_files: dir "$nl/nightlights_stable/uttar pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttar pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
    drop zstats*
	 rename *_c_code01 c_code01
    drop objectid  orig_fid
	cap drop up_*
	cap drop uttar_pradesh_*
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/uttar pradesh"
local ap_files: dir "$nl/nightlights_stable/uttar pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttar pradesh"
  rename *,lower
  gen extra = substr(prelimstring, -9, 5)
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
    gen up_version = .
    replace up_version = 1 if strpos("`file'", "east1")
    replace up_version = 2 if strpos("`file'", "east2")
    replace up_version = 3 if strpos("`file'", "west")
    local up_version = up_version
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid orig_fid
	cap drop up_*
	cap drop uttar_pradesh_*
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`up_version'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  gen up_version = .
  replace up_version = 1 if strpos("`file'", "east1")
  replace up_version = 2 if strpos("`file'", "east2")
  replace up_version = 3 if strpos("`file'", "west")
  local up_version = up_version
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`up_version'_`sat'_`year'.dta", replace
}

}  
  
  
cd "$nl/nightlights_stable/uttarakhand"
local ap_files: dir "$nl/nightlights_stable/uttarakhand" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttarakhand"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_stable/west bengal"
local ap_files: dir "$nl/nightlights_stable/west bengal" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "west bengal"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -30, 3)
    gen year = substr(prelimstring, -27, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -25, 3)
  gen year = substr(prelimstring, -22, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum variety minority range std
  rename (min max mean majority median) (lights_min lights_max lights_mean lights_majority lights_median)
  replace extra = ""
  save "$nl/nightlights_stable/all_stata/`state'_`sat'_`year'.dta", replace
}

}


*** Append all stata files
clear
cd "$nl/nightlights_stable/all_stata"
local nightlights: dir "$nl/nightlights_stable/all_stata" files "*.dta"
foreach file in `nightlights' {
  use "`file'", clear
  tostring *, replace force
  save, replace
}
local nightlights: dir "$nl/nightlights_stable/all_stata" files "*.dta"
foreach file in `nightlights' {
  append using "`file'"
}
drop  up_version area prelimstring extra 
duplicates drop
drop if c_code01 == ""
destring lights_* year, replace 
save "$nl/nightlights_stable/nightlights_all_stable.dta", replace	

}

********************************************************************************
********************************************************************************

** Step 3: Read xpct lights into Stata
{
cd "$nl/nightlights_x_pct/andhra pradesh"
local ap_files: dir "$nl/nightlights_x_pct/andhra pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "andhra pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  drop objectid zone_code count sum range std
  rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/assam"
local ap_files: dir "$nl/nightlights_x_pct/assam" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "assam"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/bihar"
local ap_files: dir "$nl/nightlights_x_pct/bihar" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "bihar"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/chhattisgarh"
local ap_files: dir "$nl/nightlights_x_pct/chhattisgarh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "chhattisgarh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/goa"
local ap_files: dir "$nl/nightlights_x_pct/goa" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "goa"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/gujarat"
local ap_files: dir "$nl/nightlights_x_pct/gujarat" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "gujarat"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/haryana"
local ap_files: dir "$nl/nightlights_x_pct/haryana" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "haryana"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/himachal pradesh"
local ap_files: dir "$nl/nightlights_x_pct/himachal pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "himachal pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/jammu kashmir"
local ap_files: dir "$nl/nightlights_x_pct/jammu kashmir" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "jammu kashmir"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/jharkhand"
local ap_files: dir "$nl/nightlights_x_pct/jharkhand" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "jharkhand"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/karnataka"
local ap_files: dir "$nl/nightlights_x_pct/karnataka" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "karnataka"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/kerala"
local ap_files: dir "$nl/nightlights_x_pct/kerala" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "kerala"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/madhya pradesh"
local ap_files: dir "$nl/nightlights_x_pct/madhya pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "madhya pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/maharashtra"
local ap_files: dir "$nl/nightlights_x_pct/maharashtra" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "maharashtra"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/manipur"
local ap_files: dir "$nl/nightlights_x_pct/manipur" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "manipur"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/orissa"
local ap_files: dir "$nl/nightlights_x_pct/orissa" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "orissa"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/punjab"
local ap_files: dir "$nl/nightlights_x_pct/punjab" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "punjab"
   gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/rajasthan"
local ap_files: dir "$nl/nightlights_x_pct/rajasthan" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "rajasthan"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/tamil nadu"
local ap_files: dir "$nl/nightlights_x_pct/tamil nadu" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "tamil nadu"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/tripura"
local ap_files: dir "$nl/nightlights_x_pct/tripura" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "tripura"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/uttar pradesh"
local ap_files: dir "$nl/nightlights_x_pct/uttar pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttar pradesh"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
    drop zstats*
	 rename *_c_code01 c_code01
    drop objectid  orig_fid
	cap drop up_*
	cap drop uttar_pradesh_*
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/uttar pradesh"
local ap_files: dir "$nl/nightlights_x_pct/uttar pradesh" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttar pradesh"
  rename *,lower
  gen extra = substr(prelimstring, -9, 5)
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
    gen up_version = .
    replace up_version = 1 if strpos("`file'", "east1")
    replace up_version = 2 if strpos("`file'", "east2")
    replace up_version = 3 if strpos("`file'", "west")
    local up_version = up_version
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid orig_fid
	cap drop up_*
	cap drop uttar_pradesh_*
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`up_version'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
  gen up_version = .
  replace up_version = 1 if strpos("`file'", "east1")
  replace up_version = 2 if strpos("`file'", "east2")
  replace up_version = 3 if strpos("`file'", "west")
  local up_version = up_version
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`up_version'_`sat'_`year'.dta", replace
}

}  
  
  
cd "$nl/nightlights_x_pct/uttarakhand"
local ap_files: dir "$nl/nightlights_x_pct/uttarakhand" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "uttarakhand"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


cd "$nl/nightlights_x_pct/west bengal"
local ap_files: dir "$nl/nightlights_x_pct/west bengal" files "*.xls"

foreach file in `ap_files' {
  import excel `file', firstrow clear
  gen prelimstring = "`file'"
  gen state = "west bengal"
  gen extra = substr(prelimstring, -9, 5)
  rename *, lower
  if extra == "extra" {
    gen satellite =  substr(prelimstring, -24, 3)
    gen year = substr(prelimstring, -21, 4)
    local state = state
    local sat = satellite
    local year = year
	drop zstats*
	 rename *_c_code01 c_code01
    drop objectid *_bdry_* orig_fid
    gen lights_min = rastervalu
	gen lights_max = rastervalu
	gen lights_mean = rastervalu
	gen lights_majority = rastervalu
	gen lights_median = rastervalu
	drop rastervalu
    save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'_extra.dta", replace
}
else {
  gen satellite =  substr(prelimstring, -19, 3)
  gen year = substr(prelimstring, -16, 4)
  local state = state
  local sat = satellite
  local year = year
drop objectid zone_code count sum range std
    rename (min max mean) (lights_min lights_max lights_mean)
  replace extra = ""
  save "$nl/nightlights_x_pct/all_stata/`state'_`sat'_`year'.dta", replace
}

}


*** Append all stata files
clear
cd "$nl/nightlights_x_pct/all_stata"
local nightlights: dir "$nl/nightlights_x_pct/all_stata" files "*.dta"
foreach file in `nightlights' {
  use "`file'", clear
  tostring *, replace force
  save, replace
}
local nightlights: dir "$nl/nightlights_x_pct/all_stata" files "*.dta"
foreach file in `nightlights' {
  append using "`file'"
}
drop  up_version area prelimstring extra 
duplicates drop
drop if c_code01 == ""
destring lights_* year, replace 
save "$nl/nightlights_x_pct/nightlights_all_x_pct.dta", replace

}

********************************************************************************
********************************************************************************

** Step 4: Finalize average visible lights dataset
{
use "$nl/nightlights_avg_vis/nightlights_all_avg_vis.dta", clear

duplicates drop
sort c_code01 year
collapse (mean) lights*, by(c_code01 state year) fast
compress
cap drop if year=="tabl"
drop if c_code01=="Pandaul" | c_code01=="0" | c_code01==" " | c_code01==""
gen st_code = substr(c_code01,1,2)
gen dt_code = substr(c_code01,3,2)
gen bk_code = substr(c_code01,5,4)
gen vi_code = substr(c_code01,9,8)
destring st_code dt_code bk_code vi_code year, replace
drop if st_code > 35 | vi_code==.

duplicates tag c_code01 year, gen(dup)
drop if dup>0 & st_code==19 & state!="west bengal"
drop if dup>0 & st_code==29 & state!="karnataka"
egen st_code_correct = mode(st_code), by(state)
drop if st_code!=st_code_correct
drop dup st_code_correct 
reshape wide lights*, i(c_code01) j(year)

egen maxlights_mean = rowmax(lights_mean*)
egen maxlights_median = rowmax(lights_median*)
egen maxlights_max = rowmax(lights_max*)
egen maxlights_min = rowmax(lights_min*)
egen maxlights_majority = rowmax(lights_majority*)

gen yearmax_mean = .
gen yearmax_median = .
gen yearmax_max = .
gen yearmax_min = .
gen yearmax_maj = .

forvalues i = 1998 / 2012 {
  replace yearmax_mean = `i' if maxlights_mean == lights_mean`i'
  replace yearmax_median = `i' if maxlights_median == lights_median`i'
  replace yearmax_max = `i' if maxlights_max == lights_max`i'
  replace yearmax_min = `i' if maxlights_min == lights_min`i'
  replace yearmax_maj = `i' if maxlights_majority == lights_majority`i'
}

egen minlights_mean = rowmin(lights_mean*)
egen minlights_median = rowmin(lights_median*)
egen minlights_max = rowmin(lights_max*)
egen minlights_min = rowmin(lights_min*)
egen minlights_majority = rowmin(lights_majority*)

gen yearmin_mean = .
gen yearmin_median = .
gen yearmin_max = .
gen yearmin_min = .
gen yearmin_maj = .

forvalues i = 1998 / 2012 {
  replace yearmin_mean = `i' if minlights_mean == lights_mean`i'
  replace yearmin_median = `i' if minlights_median == lights_median`i'
  replace yearmin_max = `i' if minlights_max == lights_max`i'
  replace yearmin_min = `i' if minlights_min == lights_min`i'
  replace yearmin_maj = `i' if minlights_majority == lights_majority`i'
}

gen biggestdiff_mean = yearmax_mean - yearmin_mean
gen biggestdiff_median = yearmax_median - yearmin_median
gen biggestdiff_max = yearmax_max - yearmin_max
gen biggestdiff_min = yearmax_min - yearmin_min
gen biggestdiff_maj = yearmax_maj - yearmin_maj

forvalues i = 2008 / 2012 {
  forvalues j = 1998 / 2003 {
    if `i' > `j' {
      gen diff_`i'_`j'_mean 	= lights_mean`i' 			- lights_mean`j'
      gen diff_`i'_`j'_median = lights_median`i' 		- lights_median`j'
      gen diff_`i'_`j'_max 		= lights_max`i' 			- lights_max`j'
      gen diff_`i'_`j'_min 		= lights_min`i' 			- lights_min`j'
      gen diff_`i'_`j'_maj 		= lights_majority`i' 	- lights_majority`j'
    }
  }
}

compress *
duplicates drop st_code dt_code vi_code, force
save "$nl/nightlights_outcomes_avg_vis.dta", replace

}

********************************************************************************
********************************************************************************

** Step 5: Finalize stable lights dataset
{
use "$nl/nightlights_stable/nightlights_all_stable.dta", clear

duplicates drop
sort c_code01 year
collapse (mean) lights*, by(c_code01 state year) fast
compress
cap drop if year=="tabl"
drop if c_code01=="Pandaul" | c_code01=="0" | c_code01==" " | c_code01==""
gen st_code = substr(c_code01,1,2)
gen dt_code = substr(c_code01,3,2)
gen bk_code = substr(c_code01,5,4)
gen vi_code = substr(c_code01,9,8)
destring st_code dt_code bk_code vi_code year, replace
drop if st_code > 35 | vi_code==.

duplicates tag c_code01 year, gen(dup)
drop if dup>0 & st_code==19 & state!="west bengal"
drop if dup>0 & st_code==29 & state!="karnataka"
egen st_code_correct = mode(st_code), by(state)
drop if st_code!=st_code_correct
drop dup st_code_correct 
reshape wide lights*, i(c_code01) j(year)

egen maxlights_mean = rowmax(lights_mean*)
egen maxlights_median = rowmax(lights_median*)
egen maxlights_max = rowmax(lights_max*)
egen maxlights_min = rowmax(lights_min*)
egen maxlights_majority = rowmax(lights_majority*)

gen yearmax_mean = .
gen yearmax_median = .
gen yearmax_max = .
gen yearmax_min = .
gen yearmax_maj = .

forvalues i = 1998 / 2012 {
  replace yearmax_mean = `i' if maxlights_mean == lights_mean`i'
  replace yearmax_median = `i' if maxlights_median == lights_median`i'
  replace yearmax_max = `i' if maxlights_max == lights_max`i'
  replace yearmax_min = `i' if maxlights_min == lights_min`i'
  replace yearmax_maj = `i' if maxlights_majority == lights_majority`i'
}

egen minlights_mean = rowmin(lights_mean*)
egen minlights_median = rowmin(lights_median*)
egen minlights_max = rowmin(lights_max*)
egen minlights_min = rowmin(lights_min*)
egen minlights_majority = rowmin(lights_majority*)

gen yearmin_mean = .
gen yearmin_median = .
gen yearmin_max = .
gen yearmin_min = .
gen yearmin_maj = .

forvalues i = 1998 / 2012 {
  replace yearmin_mean = `i' if minlights_mean == lights_mean`i'
  replace yearmin_median = `i' if minlights_median == lights_median`i'
  replace yearmin_max = `i' if minlights_max == lights_max`i'
  replace yearmin_min = `i' if minlights_min == lights_min`i'
  replace yearmin_maj = `i' if minlights_majority == lights_majority`i'
}

gen biggestdiff_mean = yearmax_mean - yearmin_mean
gen biggestdiff_median = yearmax_median - yearmin_median
gen biggestdiff_max = yearmax_max - yearmin_max
gen biggestdiff_min = yearmax_min - yearmin_min
gen biggestdiff_maj = yearmax_maj - yearmin_maj

forvalues i = 2008 / 2012 {
  forvalues j = 1998 / 2003 {
    if `i' > `j' {
      gen diff_`i'_`j'_mean 	= lights_mean`i' 			- lights_mean`j'
      gen diff_`i'_`j'_median = lights_median`i' 		- lights_median`j'
      gen diff_`i'_`j'_max 		= lights_max`i' 			- lights_max`j'
      gen diff_`i'_`j'_min 		= lights_min`i' 			- lights_min`j'
      gen diff_`i'_`j'_maj 		= lights_majority`i' 	- lights_majority`j'
    }
  }
}

compress *
duplicates drop st_code dt_code vi_code, force
save "$nl/nightlights_outcomes_stable.dta", replace

}

********************************************************************************
********************************************************************************

** Step 6: Finalize xpct lights dataset
{
use "$nl/nightlights_x_pct/nightlights_all_x_pct.dta", clear

duplicates drop
sort c_code01 year
collapse (mean) lights*, by(c_code01 state year) fast
compress
cap drop if year=="tabl"
drop if c_code01=="Pandaul" | c_code01=="0" | c_code01==" " | c_code01==""
gen st_code = substr(c_code01,1,2)
gen dt_code = substr(c_code01,3,2)
gen bk_code = substr(c_code01,5,4)
gen vi_code = substr(c_code01,9,8)
destring st_code dt_code bk_code vi_code year, replace
drop if st_code > 35 | vi_code==.

duplicates tag c_code01 year, gen(dup)
drop if dup>0 & st_code==19 & state!="west bengal"
drop if dup>0 & st_code==29 & state!="karnataka"
egen st_code_correct = mode(st_code), by(state)
drop if st_code!=st_code_correct
drop dup st_code_correct 
reshape wide lights*, i(c_code01) j(year)

egen maxlights_mean = rowmax(lights_mean*)
egen maxlights_median = rowmax(lights_median*)
egen maxlights_max = rowmax(lights_max*)
egen maxlights_min = rowmax(lights_min*)
egen maxlights_majority = rowmax(lights_majority*)

gen yearmax_mean = .
gen yearmax_median = .
gen yearmax_max = .
gen yearmax_min = .
gen yearmax_maj = .

forvalues i = 1998 / 2012 {
  replace yearmax_mean = `i' if maxlights_mean == lights_mean`i'
  replace yearmax_median = `i' if maxlights_median == lights_median`i'
  replace yearmax_max = `i' if maxlights_max == lights_max`i'
  replace yearmax_min = `i' if maxlights_min == lights_min`i'
  replace yearmax_maj = `i' if maxlights_majority == lights_majority`i'
}

egen minlights_mean = rowmin(lights_mean*)
egen minlights_median = rowmin(lights_median*)
egen minlights_max = rowmin(lights_max*)
egen minlights_min = rowmin(lights_min*)
egen minlights_majority = rowmin(lights_majority*)

gen yearmin_mean = .
gen yearmin_median = .
gen yearmin_max = .
gen yearmin_min = .
gen yearmin_maj = .

forvalues i = 1998 / 2012 {
  replace yearmin_mean = `i' if minlights_mean == lights_mean`i'
  replace yearmin_median = `i' if minlights_median == lights_median`i'
  replace yearmin_max = `i' if minlights_max == lights_max`i'
  replace yearmin_min = `i' if minlights_min == lights_min`i'
  replace yearmin_maj = `i' if minlights_majority == lights_majority`i'
}

gen biggestdiff_mean = yearmax_mean - yearmin_mean
gen biggestdiff_median = yearmax_median - yearmin_median
gen biggestdiff_max = yearmax_max - yearmin_max
gen biggestdiff_min = yearmax_min - yearmin_min
gen biggestdiff_maj = yearmax_maj - yearmin_maj

forvalues i = 2008 / 2012 {
  forvalues j = 1998 / 2003 {
    if `i' > `j' {
      gen diff_`i'_`j'_mean 	= lights_mean`i' 			- lights_mean`j'
      gen diff_`i'_`j'_median = lights_median`i' 		- lights_median`j'
      gen diff_`i'_`j'_max 		= lights_max`i' 			- lights_max`j'
      gen diff_`i'_`j'_min 		= lights_min`i' 			- lights_min`j'
      gen diff_`i'_`j'_maj 		= lights_majority`i' 	- lights_majority`j'
    }
  }
}

compress *
duplicates drop st_code dt_code vi_code, force
save "$nl/nightlights_outcomes_x_pct.dta", replace

}

********************************************************************************
********************************************************************************
